package com.sciener.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sciener.entity.User;
import com.sciener.entity.resultdata.DriverResult;
import com.sciener.entity.resultdata.LevelItem;
import com.sciener.entity.resultdata.UserDetail;
import com.sciener.entity.resultdata.Userlevel;

public class UserDaoImp {
	 
	/**
	 * ��ȡ�����û�
	 * @return
	 */
	public List<User> getAllUsers()
	{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> users = new ArrayList<User>();
		String sql = "select * from user";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next())
			{
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setUsername(rs.getString("username"));
				u.setPassword(rs.getString("password"));
				u.setEmail(rs.getString("email"));
				u.setHeader(rs.getString("header"));
				u.setNickname(rs.getString("nickname"));
				u.setCarid(rs.getInt("carid"));
				u.setLicenceid(rs.getInt("licenceid"));
				u.setSign(rs.getString("sign"));
				u.setGpsw(rs.getString("gpsw"));
				u.setGpsh(rs.getString("gpsh"));
				u.setAddress(rs.getString("address"));
				u.setSex(rs.getInt("sex"));
				users.add(u);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return users;
	}

	/**
	 * ͨ���û����ж��û��Ƿ����
	 * @param userName
	 * @return
	 */
	public boolean userNameExite(String userName) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
	
		String sql = "select * from user where username = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			while(rs.next())
			{
				return true;
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return false;
	}

	/**
	 * �жϵ�½���û���������Ƿ���ȷ
	 * @param userName
	 * @param passWord
	 * @return
	 */
	public boolean rightUserAndPwd(String userName, String passWord) {		
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user where username = ? and password = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, passWord);
			rs = ps.executeQuery();
			while(rs.next())
			{
				return true;
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return false;
	}
	/**
	 * ����û������뷵���û�id
	 * @param userName
	 * @param passWord
	 * @return
	 */
	public int getUseridByusernameandpassword(String userName, String passWord) {		
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select id from user where username = ? and password = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, passWord);
			rs = ps.executeQuery();
			while(rs.next())
			{
				return rs.getInt("id");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return -1;
	}

	/**
	 * �����û���Ϣ
	 * @param userName
	 * @param passWord
	 * @return
	 */
	public boolean insertUser(User user) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		boolean complement = false;
		String sql = "insert into user(username,password,email,header,nickname,"
			+ "carid,licenceid,sign,gpsw,gpsh,address,sex) value(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getHeader());
			ps.setString(5, user.getNickname());		
			ps.setInt(6, user.getCarid());
			ps.setInt(7, user.getLicenceid());
			ps.setString(8, user.getSign());
			ps.setString(9, user.getGpsw());
			ps.setString(10, user.getGpsh());
			ps.setString(11, user.getAddress());
			ps.setInt(12, user.getSex());
			complement = ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		return complement;
	}
	
	/**
	 * ɾ���û�
	 * @param user
	 * @return
	 */
	public boolean deleteUser(User user)
	{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		boolean complement = false;
		String sql = "delete from user where username =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			
			complement = ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		return complement;
	}
	
	/**
	 * �����û���Ϣ
	 * @param user
	 * @return
	 */
	public boolean updateUser(User user)
	{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		int complement = 0;
		String sql = "update user set username =?, password =?, email =?, header =?, nickname =?,"
			+ "carid =?, licenceid =?, sign =?, gpsw =?, gpsh =?, address =?, sex =? " 
			+ "where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getHeader());
			ps.setString(5, user.getNickname());		
			ps.setInt(6, user.getCarid());
			ps.setInt(7, user.getLicenceid());
			ps.setString(8, user.getSign());
			ps.setString(9, user.getGpsw());
			ps.setString(10, user.getGpsh());
			ps.setString(11, user.getAddress());
			ps.setInt(12, user.getSex());
			ps.setInt(13, user.getId());
		
			complement = ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		
		if(1 == complement)
		{
			return true;
		}
		return false;
	}
	
	/**
	 * ͨ���û����ȡ�û�����
	 * @param userName
	 */
	public User getUserbyUserName(String userName)
	{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user where username = ?";
		User user = new User();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userName);
			rs = ps.executeQuery();
			while(rs.next())
			{
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setHeader(rs.getString("header"));
				user.setNickname(rs.getString("nickname"));
				user.setCarid(rs.getInt("carid"));
				user.setLicenceid(rs.getInt("licenceid"));
				user.setSign(rs.getString("sign"));
				user.setGpsw(rs.getString("gpsw"));
				user.setGpsh(rs.getString("gpsh"));
				user.setAddress(rs.getString("address"));
				user.setSex(rs.getInt("sex"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return user;
	}
	
	/**
	 * ͨ��id��ȡ�û�����
	 * @param userName
	 */
	public User getUserbyUserId(int id)
	{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from user where id = ?";
		User user = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next())
			{
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setHeader(rs.getString("header"));
				user.setNickname(rs.getString("nickname"));
				user.setCarid(rs.getInt("carid"));
				user.setLicenceid(rs.getInt("licenceid"));
				user.setSign(rs.getString("sign"));
				user.setGpsw(rs.getString("gpsw"));
				user.setGpsh(rs.getString("gpsh"));
				user.setAddress(rs.getString("address"));
				user.setSex(rs.getInt("sex"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return user;
	}

	public UserDetail getrecllerdetail(Integer userid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select u.id,u.header,u.username,u.agentlevel," +
				"u.agentcomment,c.brand,c.mode,l.level,u.driveryear," +
				"u.driverlevel,u.drivercomment,u.address,u.sign from user as u " +
				"left join car as c on u.id = c.userid left join licence as l on u.id=l.userid" +
				" where u.id=?";
		UserDetail user = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				user = new UserDetail();
				user.setId(rs.getInt("id"));
				user.setHeader(rs.getString("header"));
				user.setUsername(rs.getString("username"));
				user.setAgentlevel(rs.getString("agentlevel"));
				user.setAgentjudge(rs.getString("agentcomment"));
				user.setCarbrand(rs.getString("brand"));
				user.setCarmodel(rs.getString("mode"));
				user.setLicencelevel(rs.getString("level"));
				user.setDriveryear(rs.getString("driveryear"));
				user.setDriverlevel(rs.getString("driverlevel"));
				user.setDriverjudge(rs.getString("drivercomment"));
				user.setSign(rs.getString("sign"));
				user.setAddress(rs.getString("address"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return user;
	}

	public Userlevel getuserlevelsbyid(Integer userid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select id,header,nickname,agentcomment,drivercomment,agentstar,driverstar from user where id=?";
		Userlevel level = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				level = new Userlevel();
				level.setId(rs.getInt("id"));
				level.setUserheader(rs.getString("header"));
				level.setNickname(rs.getString("nickname"));
				level.setAgentcomment(rs.getString("agentcomment"));
				level.setDrivercomment(rs.getString("drivercomment"));
				level.setAgentstar(rs.getString("agentstar"));
				level.setDriverstar(rs.getString("driverstar"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		
		return level;
	}

	public List<LevelItem> getlevellistbyid(Integer userid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<LevelItem> levels = new ArrayList<LevelItem>();
		String sql = "select j.id,j.topicid,j.usergive,j.recallgive,j.usersay,j.recallsay," +
				"u.header,u.nickname,t.type,t.title,u2.nickname as rnickname " +
				" from judge as j left join user as u on j.userid = u.id " +
				"left join topic as t on t.id = j.topicid " +
				"left join user as u2 on j.recallid = u2.id where u.id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				LevelItem u = new LevelItem();
				u.setId(rs.getInt("id"));
				u.setTopicid(rs.getInt("topicid"));
				u.setDeclaregive(rs.getString("usergive"));
				u.setRecallergive(rs.getString("recallgive"));
				u.setDeclaresay(rs.getString("usersay"));
				u.setRecallersay(rs.getString("recallsay"));
				u.setDeclareheader(rs.getString("header"));
				u.setDecalrenickname(rs.getString("nickname"));
				u.setTopictype(rs.getString("type"));
				u.setTopictitle(rs.getString("title"));
				u.setRecallernickname(rs.getString("rnickname"));
				
				levels.add(u);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return levels;
	}

	public void insertagentjudge(String topicid, String give, String rating,
			String saysth) throws Exception{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update judge set usergive=?,usersay=?,userscore=?  where topicid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, give);
			ps.setString(2, saysth);
			ps.setString(3, rating);
			ps.setInt(4, Integer.valueOf(topicid));
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
			
		} finally{
			ps.close();
			conn.close();
		}
		
		
	}

	public void insertdriverjudge(String topicid, String give, String rating,
			String saysth) throws Exception{
		
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update judge set recallgive=?,recallsay=?,recallscore=?  where topicid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, give);
			ps.setString(2, saysth);
			ps.setString(3, rating);
			ps.setInt(4, Integer.valueOf(topicid));
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
			
		} finally{
			ps.close();
			conn.close();
		}
		
	}

	public void updateTopicstate(String topicid, int state) throws Exception{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update topic set state=? where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, state);
			ps.setInt(2, Integer.valueOf(topicid));
			
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
			
		} finally{
			ps.close();
			conn.close();
		}
		
	}

	public int findtopicstate(String topicid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int state = 0;
		String sql = "select state from topic where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(topicid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				state = rs.getInt("state");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return state;
	}

	public List<LevelItem> getdriverlevellistbyid(Integer userid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<LevelItem> levels = new ArrayList<LevelItem>();
		String sql = "select j.id,j.topicid,j.usergive,j.recallgive,j.usersay,j.recallsay," +
				"u.header,u.nickname,t.type,t.title,u2.nickname as rnickname " +
				" from judge as j left join user as u on j.userid = u.id " +
				"left join topic as t on t.id = j.topicid " +
				"left join user as u2 on j.recallid = u2.id where j.recallid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				LevelItem u = new LevelItem();
				u.setId(rs.getInt("id"));
				u.setTopicid(rs.getInt("topicid"));
				u.setDeclaregive(rs.getString("usergive"));
				u.setRecallergive(rs.getString("recallgive"));
				u.setDeclaresay(rs.getString("usersay"));
				u.setRecallersay(rs.getString("recallsay"));
				u.setDeclareheader(rs.getString("header"));
				u.setDecalrenickname(rs.getString("nickname"));
				u.setTopictype(rs.getString("type"));
				u.setTopictitle(rs.getString("title"));
				u.setRecallernickname(rs.getString("rnickname"));
				
				levels.add(u);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return levels;
	}

	public void updateUserinfo(Integer userid, String prop, String content) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update user set "+prop+"='"+content+"'" 
			+ " where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}

	public void updateCarinfo(Integer userid, String prop, String content) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update car set "+prop+"='"+content+"'" 
			+ " where userid =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}

	public void updateCarVerify(Integer userid, int isverify) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update car set isverify=?" 
			+ " where userid =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, isverify);
			ps.setInt(2, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
	}

	public void updateLicenceinfo(Integer userid, String prop, String content)throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update licence set "+prop+"='"+content+"'" 
			+ " where userid =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	public void updateLicenceVerify(Integer userid, int isverify) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update licence set isverify=?" 
			+ " where userid =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, isverify);
			ps.setInt(2, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
	}

	public void updateHeaderByuserid(Integer userid, String photoPath)  throws Exception{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update user set header=?" 
			+ " where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, photoPath);
			ps.setInt(2, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}

	public boolean findLocationByuserid(Integer userid) {
		boolean haslocation = false;
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from roundpeople where userid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				haslocation = true;
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return haslocation;
	}

	public void updateLocationByuserid(Double longtitude, Double latitude,
			Integer userid) throws Exception{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update roundpeople set longtitude=?,latitude=?" 
			+ " where userid =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, longtitude);
			ps.setDouble(2, latitude);
			ps.setInt(3, userid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}

	public void insertUserLocation(Double longtitude, Double latitude,
			Integer userid) throws Exception{
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "insert into roundpeople(userid,longtitude,latitude)"
			+ " value(?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setDouble(2, longtitude);
			ps.setDouble(3, latitude);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} 
		
		
	}
	
	public List<DriverResult> getRoundDrivers(double minLat, double minLng, double maxLat, double maxLng,double centerlong,double centerlat,Integer userid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DriverResult> drivers = new ArrayList<DriverResult>();
		String sql = "select r.userid,header,nickname,longtitude,latitude,sign,driveryear,brand,mode,ACOS(SIN((? * 3.1415) / 180 ) * SIN((latitude * 3.1415) / 180 )   +COS((? * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) * COS((? * 3.1415) / 180 - (longtitude * 3.1415) / 180 ) )   * 6380 as distance from roundpeople as r " +
				" join user as u on u.id=r.userid join car as c on c.userid = r.userid where latitude <> 0" +
				"  and longtitude > ?  and longtitude < ?  " +
				"and latitude > ?  and latitude < ?  and r.userid <> ?" +
				"  order by ACOS(SIN((? * 3.1415) / 180 ) * SIN((latitude * 3.1415) / 180 )   +COS((? * 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) * COS((? * 3.1415) / 180 - (longtitude * 3.1415) / 180 ) )   * 6380 asc limit 20  ";
		try {
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, centerlat);
			ps.setDouble(2, centerlat);
			ps.setDouble(3, centerlong);
			
			ps.setDouble(4, minLng);
			ps.setDouble(5, maxLng);
			ps.setDouble(6, minLat);
			ps.setDouble(7, maxLat);
			ps.setInt(8, userid);
			
			ps.setDouble(9, centerlat);
			ps.setDouble(10, centerlat);
			ps.setDouble(11, centerlong);
			rs = ps.executeQuery();
			while(rs.next())
			{
				DriverResult u = new DriverResult();
				u.setUserid(rs.getInt("userid"));
				u.setDriverheader(rs.getString("header"));
				u.setDrivername(rs.getString("nickname"));
				u.setLongtitude(rs.getDouble("longtitude"));
				u.setLatitude(rs.getDouble("latitude"));
				u.setSign(rs.getString("sign"));
				u.setCar(rs.getString("brand"));
				u.setCarmode(rs.getString("mode"));
				u.setDriveryear(String.valueOf(rs.getInt("driveryear")));
				u.setDistance(rs.getDouble("distance"));
				drivers.add(u);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return drivers;
		
	}

	public int findrecalleridByTopicid(String topicid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int recallid = 0;
		String sql = "select recallid from judge where topicid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(topicid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				recallid = rs.getInt("recallid");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return recallid;
		
	}
	
	/**
	 * 计算该人的司机评价有几条
	 * @param recallerid
	 * @return
	 */
	public int getDriverJudgeCount(int recallerid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "select count(*) from judge where recallid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(recallerid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				count = rs.getInt("count(*)");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return count;
	}
	
	
	/**
	 * 司机好评有几条
	 * @param recallerid
	 * @return
	 */
	public int getDriverJudgegoodCount(int recallerid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "select count(*) from judge where recallid=? and usergive='好评' ";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(recallerid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				count = rs.getInt("count(*)");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return count;
	}

	public void updateDriverJudgeByuserid(int recallerid, String goodratio) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update user set drivercomment=?" 
			+ " where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodratio);
			ps.setInt(2, recallerid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	public int findagentidByTopicid(String topicid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int userid = 0;
		String sql = "select userid from judge where topicid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(topicid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				userid = rs.getInt("userid");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return userid;
		
	}

	public int getAgentJudgeCount(int agentid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "select count(*) from judge where userid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(agentid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				count = rs.getInt("count(*)");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return count;
	}

	public int getAgentJudgegoodCount(int agentid) {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String sql = "select count(*) from judge where userid=? and recallgive='好评' ";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, Integer.valueOf(agentid));
			rs = ps.executeQuery();
			while(rs.next())
			{
				count = rs.getInt("count(*)");
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			
		}
		return count;
	}

	public void updateAgentJudgeByuserid(int agentid, String goodratio) throws Exception {
		Connection conn = DBUtilc3p0.getConnection();
		PreparedStatement ps = null;
		String sql = "update user set agentcomment=?" 
			+ " where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, goodratio);
			ps.setInt(2, agentid);
			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
}
